using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
using System.Data;
using System.Collections.Generic;

namespace Infrastructure
{
    /// <summary>
    /// Excel辅助类
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 将文件流转为Excel工作薄
        /// </summary>
        /// <param name="stream">数据流</param>
        /// <param name="fileName">文件名(含后缀名)，用于区分excel版本</param>
        /// <returns>工作簿</returns>
        public static IWorkbook ConvertToExcel(Stream stream, string fileName)
        {
            // 文件名忽略大小写
            fileName = fileName.ToLower();

            // 2007+版本
            if (fileName.EndsWith(".xlsx"))
            {
                return new XSSFWorkbook(stream);
            }
            // 2003版本
            else if (fileName.EndsWith(".xls"))
            {
                return new HSSFWorkbook(stream);
            }

            throw new Exception("file is not excel file");
        }

        /// <summary>
        /// 读取Sheet的所有数据
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="headerRowIndex">列名的行索引(从0开始)，小于0时表示无列名的行。
        /// 该参数将应用于所有Sheet。</param>
        /// <returns>包含Sheet数据的DataTable</returns>
        public static DataTable GetSheetData(this ISheet sheet, int headerRowIndex = -1)
        {
            // 设置数据表名称
            DataTable data = new DataTable(sheet.SheetName);

            int startRow = 0;

            // 总列数，默认为0
            int colCount = 0;

            // 总行数
            int rowCount = sheet.LastRowNum;

            // 遍历所有行，得到列数，取最大列数
            for (int i = startRow; i <= rowCount; ++i)
            {
                // 得到行
                IRow row = sheet.GetRow(i);

                // 没有数据行不取列数　　
                if (row == null)
                    continue;
                else
                {
                    // 行的列数大于当前列数
                    if (row.LastCellNum > colCount)
                        colCount = row.LastCellNum;
                }
            }

            // 设置了列名行
            if (headerRowIndex >= 0)
            {
                // 得到列名行
                IRow headerRow = sheet.GetRow(headerRowIndex);

                // 这里需要验证行是否为空
                // 为空一般情况下是sheet为空或只有非数值数据(图片等)
                if (headerRow != null)
                {
                    // 遍历行的每一列
                    for (int i = headerRow.FirstCellNum; i < colCount; i++)
                    {
                        // 得到单元格
                        ICell cell = headerRow.GetCell(i);

                        CellData headerCellData = cell.GetCellValue();

                        if (headerCellData != null)
                        {
                            // 列名重复，列名增加一个后缀
                            if (data.Columns.Contains(headerCellData.CellValue.ToString()))
                            {
                                headerCellData.CellValue += DateTime.Now.Ticks;
                            }

                            // 添加DataTable的列
                            // 单元格的值作为列名
                            DataColumn column = new DataColumn(headerCellData.CellValue.ToString());

                            data.Columns.Add(column);
                        }
                        else
                        {
                            // 添加DataTable的列
                            // 列顺序作为列名
                            DataColumn column = new DataColumn("Column" + (i + 1).ToString());
                            data.Columns.Add(column);
                        }
                    }

                    // 数据行从列名行后开始
                    startRow = headerRowIndex + 1;
                }
            }
            else
            {
                for (int i = 0; i < colCount; i++)
                {
                    // 添加DataTable的列
                    // 列顺序作为列名
                    DataColumn column = new DataColumn("Column" + (i + 1).ToString());
                    data.Columns.Add(column);
                }

                // 未设置列名行，数据行从第一行开始
                startRow = sheet.FirstRowNum;
            }

            // 遍历所有行
            for (int i = startRow; i <= rowCount; ++i)
            {
                // 得到数据行
                IRow row = sheet.GetRow(i);

                // 没有数据的行默认是null　　　　　　　
                if (row == null)
                    continue;

                DataRow dataRow = data.NewRow();

                // 遍历行的每个单元格
                for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
                {
                    // 得到单元格的值
                    CellData cellData = row.GetCell(j).GetCellValue();

                    // 单元格不为空，则单元格的值填充到DataRow中
                    // 没有数据的单元格都默认是null
                    if (cellData != null)
                        dataRow[j] = cellData.CellValue;
                }

                data.Rows.Add(dataRow);
            }

            return data;
        }

        /// <summary>
        /// 读取WorkBook中的所有数据
        /// </summary>
        /// <param name="workBook">workBook对象</param>
        /// <param name="sheetName">指定的工作表名称，未指定则读取所有工作表</param>
        /// <param name="headerRowIndex">列名的行索引(从0开始)，小于0时表示无列名的行。
        /// 该参数将应用于所有Sheet。</param>
        /// <returns>WorkBook</returns>
        public static DataSet GetAllData(this IWorkbook workBook, string sheetName, int headerRowIndex = -1)
        {
            List<ISheet> sheets = new List<ISheet>();
            DataSet dataSet = new DataSet();

            // 指定了工作表
            if (!string.IsNullOrEmpty(sheetName))
            {
                // 取指定的工作表
                ISheet sheet = workBook.GetSheet(sheetName);

                // 指定工作表存在
                if (sheet != null)
                    sheets.Add(sheet);
            }
            else //未指定则取所有工作表
            {
                for (int i = 0; i < workBook.NumberOfSheets; i++)
                {
                    // 取指定的工作表
                    ISheet sheet = workBook.GetSheetAt(i);

                    sheets.Add(sheet);
                }
            }

            foreach (ISheet sheet in sheets)
            {
                DataTable data = sheet.GetSheetData(headerRowIndex);

                dataSet.Tables.Add(data);
            }

            // 返回结果
            return dataSet;
        }

        /// <summary>
        /// 获取单元格数据类型及值
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="cellLocation">单元格位置：如A1</param>
        /// <returns>单元格数据类型及值</returns>
        public static CellData GetCellValue(this ISheet sheet, string cellLocation)
        {
            // 设置要获取的单元格
            CellReference cellReference = new CellReference(cellLocation);

            if (cellReference == null) return null;

            // 得到单元格
            IRow row = sheet.GetRow(cellReference.Row);
            if (row == null) return null;

            ICell cell = row.GetCell(cellReference.Col);
            if (cell == null) return null;

            return cell.GetCellValue();
        }

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>单元格数据对象</returns>
        public static CellData GetCellValue(this ICell cell)
        {
            if (cell == null) return null;

            // 获取单元格数据类型
            CellType cellType = cell.CellType;

            // 如果单元格是公式单元格，得到其最终的数据类型
            if (cellType == CellType.Formula)
            {
                // 以最终的结果类型作为单元格数据类型
                cellType = cell.CachedFormulaResultType;
            }

            // 要返回的结果
            CellData result = new CellData();
            result.CellType = cellType;

            // 根据数据类型得到相应的值
            switch (cellType)
            {
                case CellType.String:
                case CellType.Blank:
                    result.CellValue = cell.StringCellValue;
                    break;
                case CellType.Numeric:
                    // 日期数据也是数字类型，要特殊处理
                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                    {
                        result.CellValue = cell.DateCellValue;
                    }
                    else
                    {
                        // 数据格式，用于格式化小数位数 格式为0.00_代表2位小数
                        string formatString = cell.CellStyle.GetDataFormatString().Replace("_ ", "");

                        // 格式如果为常规格式，则直接返回数据
                        if (formatString == "General")
                        {
                            result.CellValue = cell.NumericCellValue.ToString();
                        }
                        else
                        {
                            // 将格式化后的数据作为结果
                            // 以字符串形式作为结果，以保证和看到的格式是一致的
                            // 如果以小数作为结果，格式化后的数据和展示的数据不一定致
                            // 如：保留两个小数的2.00，数值只为2，看不到后面的小数部分了
                            result.CellValue = cell.NumericCellValue.ToString(formatString);
                        }
                    }
                    break;
                case CellType.Boolean:
                    result.CellValue = cell.BooleanCellValue;
                    break;
            }

            // 返回数据结果
            return result;
        }

        #region Property

        /// <summary>
        /// 单元格数据模型
        /// </summary>
        public class CellData
        {
            /// <summary>
            /// 单元格类型
            /// </summary>
            public CellType CellType { get; set; }

            /// <summary>
            /// 单元格数据值
            /// </summary>
            public dynamic CellValue { get; set; }
        }

        #endregion
    }
}
